#!/usr/bin/env node

/**
 * 创建完全符合V2 schema的正确Excel文件
 */

const XLSX = require('xlsx');
const fs = require('fs');
const path = require('path');
const axios = require('axios');
const FormData = require('form-data');

class CorrectExcelCreator {
  constructor() {
    this.baseDir = '/Users/andylau/Dropbox/AppDevelopment/CMB250716';
    this.token = 'eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9.eyJ1c2VySWQiOjEsImVtYWlsIjoiYWRtaW5AZXhhbXBsZS5jb20iLCJtZW1iZXJzaGlwTGV2ZWwiOiJhZG1pbiIsImlhdCI6MTc1NzE2NDAyMCwiZXhwIjoxNzU3NzY4ODIwLCJpc3MiOiJjdS1tci1idWxsLWFwaSJ9.2pqn01o6rqlOOoOGaEPXNtwIoK-A86kPty3KWnyvY0Q';
    this.timestamp = Date.now();
  }

  log(message, type = 'info') {
    const timestamp = new Date().toISOString();
    const prefix = {
      'info': '🔍',
      'success': '✅',
      'error': '❌',
      'fix': '🔧'
    }[type] || 'ℹ️';
    console.log(`${prefix} [${timestamp}] ${message}`);
  }

  createCorrectExcel() {
    this.log('创建完全符合V2 schema的Excel文件');
    
    const workbook = XLSX.utils.book_new();
    
    // Dishes工作表 - 严格按照V2 schema
    const dishesHeaders = [
      'dish_sku',
      'dish_slug', 
      'name_zh',
      'name_en',
      'category',
      'base_status',
      'description_zh',
      'description_en',
      'ingredients_zh',
      'ingredients_en',
      'tags',
      'popularity'
    ];
    
    const dishesData = [
      dishesHeaders,
      [
        `CORRECT_${this.timestamp}_001`,
        `correct-burger-${this.timestamp}`,
        `正确汉堡_${this.timestamp}`,
        `Correct Burger ${this.timestamp}`,
        '主食',
        'active',
        '这是正确格式的汉堡描述',
        'This is correct format burger description',
        '牛肉,生菜,面包',
        'Beef, Lettuce, Bread',
        '热门,新品',
        '85'
      ],
      [
        `CORRECT_${this.timestamp}_002`,
        `correct-drink-${this.timestamp}`,
        `正确饮料_${this.timestamp}`,
        `Correct Drink ${this.timestamp}`,
        '饮品',
        'active',
        '这是正确格式的饮料描述',
        'This is correct format drink description',
        '水,糖,柠檬',
        'Water, Sugar, Lemon',
        '清爽',
        '75'
      ]
    ];
    
    const dishesWS = XLSX.utils.aoa_to_sheet(dishesData);
    XLSX.utils.book_append_sheet(workbook, dishesWS, 'Dishes');
    
    // Variants工作表 - 严格按照V2 schema
    const variantsHeaders = [
      'dish_sku',
      'name_zh',
      'variant_code',
      'variant_name',
      'price',
      'compare_at_price',
      'is_default',
      'stock'
    ];
    
    const variantsData = [
      variantsHeaders,
      [
        `CORRECT_${this.timestamp}_001`,
        '标准',
        'STD-001',
        'Standard',
        '25.99',
        '',
        'true',
        '100'
      ],
      [
        `CORRECT_${this.timestamp}_001`,
        '大份',
        'LRG-001', 
        'Large',
        '29.99',
        '32.99',
        'false',
        '80'
      ],
      [
        `CORRECT_${this.timestamp}_002`,
        '中杯',
        'MED-001',
        'Medium',
        '12.99',
        '',
        'true',
        '150'
      ]
    ];
    
    const variantsWS = XLSX.utils.aoa_to_sheet(variantsData);
    XLSX.utils.book_append_sheet(workbook, variantsWS, 'Variants');
    
    // Options工作表 - 严格按照V2 schema
    const optionsHeaders = [
      'dish_sku',
      'name_zh',
      'group_code',
      'group_name',
      'required',
      'value_code',
      'value_name',
      'price_delta'
    ];
    
    const optionsData = [
      optionsHeaders,
      [
        `CORRECT_${this.timestamp}_001`,
        '配菜选择',
        'SIDE',
        '配菜',
        'false',
        'FRIES',
        '薯条',
        '3.00'
      ],
      [
        `CORRECT_${this.timestamp}_001`,
        '饮料选择',
        'DRINK',
        '饮料',
        'true',
        'COKE',
        '可乐',
        '0'
      ],
      [
        `CORRECT_${this.timestamp}_002`,
        '温度选择',
        'TEMP',
        '温度',
        'true',
        'ICE',
        '冰',
        '0'
      ]
    ];
    
    const optionsWS = XLSX.utils.aoa_to_sheet(optionsData);
    XLSX.utils.book_append_sheet(workbook, optionsWS, 'Options');
    
    const fileName = `correct_excel_${this.timestamp}.xlsx`;
    const filePath = path.join(this.baseDir, fileName);
    
    XLSX.writeFile(workbook, filePath);
    
    this.log(`正确格式Excel文件已创建: ${fileName}`, 'success');
    return { fileName, filePath };
  }

  async testImport(filePath) {
    this.log('测试正确格式Excel文件导入');
    
    try {
      // 1. 验证
      const form = new FormData();
      form.append('file', fs.createReadStream(filePath));
      
      const validateResponse = await axios.post('http://localhost:3001/api/admin/menu-import/v2/validate', form, {
        headers: {
          ...form.getHeaders(),
          'Authorization': `Bearer ${this.token}`
        },
        timeout: 60000
      });
      
      this.log('验证响应: ' + JSON.stringify(validateResponse.data));
      
      const jobId = validateResponse.data.job_id;
      if (!jobId) {
        throw new Error('未获取到任务ID');
      }
      
      // 2. 等待验证完成
      let attempts = 0;
      let jobStatus = 'validating';
      
      while (jobStatus === 'validating' && attempts < 30) {
        await new Promise(resolve => setTimeout(resolve, 1000));
        
        const statusResponse = await axios.get(`http://localhost:3001/api/admin/menu-import/v2/jobs/${jobId}`, {
          headers: { 'Authorization': `Bearer ${this.token}` }
        });
        
        jobStatus = statusResponse.data.status;
        this.log(`第${attempts + 1}次检查，状态: ${jobStatus}`);
        
        if (jobStatus === 'validated') {
          this.log('验证成功！', 'success');
          
          // 3. 提交导入
          const commitResponse = await axios.post(`http://localhost:3001/api/admin/menu-import/v2/commit?job_id=${jobId}`, {}, {
            headers: {
              'Authorization': `Bearer ${this.token}`,
              'Content-Type': 'application/json'
            },
            timeout: 60000
          });
          
          this.log('提交响应: ' + JSON.stringify(commitResponse.data));
          
          if (commitResponse.data.success) {
            this.log('导入成功！', 'success');
            return true;
          } else {
            this.log('提交失败: ' + commitResponse.data.message, 'error');
            return false;
          }
        } else if (jobStatus === 'failed') {
          this.log('验证失败！', 'error');
          this.log('错误信息: ' + JSON.stringify(statusResponse.data), 'error');
          return false;
        }
        
        attempts++;
      }
      
      throw new Error('验证超时');
      
    } catch (error) {
      this.log('导入测试失败: ' + error.message, 'error');
      if (error.response && error.response.data) {
        this.log('错误详情: ' + JSON.stringify(error.response.data), 'error');
      }
      return false;
    }
  }

  async checkDatabase() {
    this.log('检查数据库状态');
    
    try {
      const response = await axios.get('http://localhost:3001/api/menu/public/items', {
        headers: { 'Authorization': `Bearer ${this.token}` }
      });
      
      const items = response.data.data.items;
      this.log(`数据库中共有 ${items.length} 条数据`);
      
      // 查找新导入的数据
      const newItems = items.filter(item => item.name.includes(`${this.timestamp}`));
      if (newItems.length > 0) {
        this.log(`找到 ${newItems.length} 条新导入的数据:`, 'success');
        newItems.forEach(item => {
          this.log(`- ${item.name}: ${item.description}`);
        });
        return true;
      } else {
        this.log('未找到新导入的数据', 'error');
        return false;
      }
    } catch (error) {
      this.log('数据库检查失败: ' + error.message, 'error');
      return false;
    }
  }

  async run() {
    console.log('\n' + '='.repeat(80));
    console.log('🔧 创建并测试正确格式的Excel文件');
    console.log('🎯 严格按照V2 schema创建Excel，确保导入成功');
    console.log('='.repeat(80) + '\n');
    
    try {
      // 1. 创建正确格式的Excel文件
      const { filePath } = this.createCorrectExcel();
      
      // 2. 测试导入
      const importSuccess = await this.testImport(filePath);
      
      // 3. 检查数据库
      const dbCheck = await this.checkDatabase();
      
      console.log('\n' + '='.repeat(80));
      if (importSuccess && dbCheck) {
        console.log('🎉 Excel导入修复成功！');
        console.log('✅ 正确格式的Excel文件已创建并成功导入');
        console.log('✅ 数据已正确保存到数据库');
        console.log('💡 现在您可以使用相同的格式创建您的Excel文件');
        
        console.log('\n📋 正确的Excel格式要求:');
        console.log('工作表1 - Dishes: dish_sku, dish_slug, name_zh, name_en, category, base_status, description_zh, description_en, ingredients_zh, ingredients_en, tags, popularity');
        console.log('工作表2 - Variants: dish_sku, name_zh, variant_code, variant_name, price, compare_at_price, is_default, stock');
        console.log('工作表3 - Options: dish_sku, name_zh, group_code, group_name, required, value_code, value_name, price_delta');
      } else {
        console.log('❌ Excel导入仍然失败');
        console.log('💡 请检查上面的错误信息');
      }
      console.log('='.repeat(80) + '\n');
      
    } catch (error) {
      console.log('\n' + '='.repeat(80));
      console.log('❌ 创建正确Excel文件失败');
      console.log('错误:', error.message);
      console.log('='.repeat(80) + '\n');
    }
  }
}

// 立即运行
if (require.main === module) {
  const creator = new CorrectExcelCreator();
  creator.run().catch(console.error);
}

module.exports = CorrectExcelCreator;